package org.easy.poi.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

/**
 * 
 * @Title: ExcelBuilder.java
 * @Package org.easy.poi.excel
 * @Description: excel 生成
 * @author linNaibin
 * @date 2020年10月9日 下午4:39:52
 * @version V1.0
 */
public class ExcelBuilder {

	private final static String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";// 默认日期格式
	private final static int DEFAULT_COLOUMN_WIDTH = 17;
	private final static int EXCEL_MAX_ROWS = 3200 ; // 32767

	/**
	 * 
	 * @param headArray
	 * @param jsonArray
	 * @param out
	 */
	public static void buildxlsx(String[] headArray, JSONArray jsonArray, OutputStream out) {

		buildxlsx(null, headArray, jsonArray, DEFAULT_COLOUMN_WIDTH, out);
	}

	/**
	 * 导出Excel 2007 OOXML (.xlsx)格式
	 * 
	 * @param title       标题行
	 * @param headMap     属性-列头
	 * @param jsonArray   数据集
	 * @param datePattern 日期格式，传null值则默认 年月日
	 * @param colWidth    列宽 默认 至少17个字节
	 * @param out         输出流
	 */
	public static void buildxlsx(String title, String[] headArray, JSONArray jsonArray, int colWidth,
			OutputStream out) {

		// 声明一个工作薄
		SXSSFWorkbook workbook = new SXSSFWorkbook(1000);// 缓存
		workbook.setCompressTempFiles(true);

		// 表头样式
		CellStyle titleStyle = workbook.createCellStyle();

		titleStyle.setAlignment(HorizontalAlignment.CENTER);

		Font titleFont = workbook.createFont();
		titleFont.setFontHeightInPoints((short) 20);
		// titleFont.setBoldweight((short) 700);
		titleStyle.setFont(titleFont);
		// 列头样式
		CellStyle headerStyle = workbook.createCellStyle();
		// headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		// headerStyle.setBorderBottom(BorderStyle.THIN);
		// headerStyle.setBorderLeft(BorderStyle.THIN);
		// headerStyle.setBorderRight(BorderStyle.THIN);
		// headerStyle.setBorderTop(BorderStyle.THIN);
		// 居中
		headerStyle.setAlignment(HorizontalAlignment.CENTER);

		Font headerFont = workbook.createFont();
		headerFont.setFontHeightInPoints((short) 12);
		// headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		headerStyle.setFont(headerFont);
		// 单元格样式
		CellStyle cellStyle = workbook.createCellStyle();
		// heise
		// cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

//		cellStyle.setBorderBottom(BorderStyle.DASH_DOT);
//		cellStyle.setBorderLeft(BorderStyle.THIN);
//		cellStyle.setBorderRight(BorderStyle.THIN);
//		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		Font cellFont = workbook.createFont();
		// cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		cellStyle.setFont(cellFont);
		// 生成一个(带标题)表格
		SXSSFSheet sheet = workbook.createSheet();
		// 设置列宽
		int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;// 至少字节数
		int[] arrColWidth = new int[headArray.length];
		// 产生表格标题行,以及设置列宽
		int ii = 0;

		for (int i = 0; i < headArray.length; i++) {
			int bytes = headArray[i].getBytes().length;
			arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
			sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
			ii++;
		}

		// 遍历集合数据，产生数据行
		int rowIndex = 0;
		for (Object obj : jsonArray) {
			if (rowIndex == EXCEL_MAX_ROWS || rowIndex == 0) {
				if (rowIndex != 0) {
					sheet = workbook.createSheet();// 如果数据超过了，则在第二页显示
				}
				SXSSFRow headerRow = null;
				if (title != null) {
					// 表头 rowIndex=0
					SXSSFRow titleRow = sheet.createRow(0);
					titleRow.createCell(0).setCellValue(title);
					titleRow.getCell(0).setCellStyle(titleStyle);
					sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headArray.length - 1));
					// 列头 rowIndex =1
					headerRow = sheet.createRow(1);
					rowIndex = 2;// 数据内容从 rowIndex=2开始

				} else {
					// 列头 rowIndex =0
					headerRow = sheet.createRow(0);
					rowIndex = 1;// 数据内容从 rowIndex=2开始

				}

				for (int i = 0; i < headArray.length; i++) {
					headerRow.createCell(i).setCellValue(headArray[i]);
					headerRow.getCell(i).setCellStyle(headerStyle);

				}
			}
			JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
			SXSSFRow dataRow = sheet.createRow(rowIndex);
			for (int i = 0; i < headArray.length; i++) {
				SXSSFCell newCell = dataRow.createCell(i);

				Object o = jo.get(headArray[i]);
				String cellValue = "";
				if (o == null)
					cellValue = "";
				else if (o instanceof Date)
					cellValue = new SimpleDateFormat(DEFAULT_DATE_PATTERN).format(o);
				else if (o instanceof Float || o instanceof Double)
					cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
				else
					cellValue = o.toString();

				newCell.setCellValue(cellValue);
				newCell.setCellStyle(cellStyle);
			}
			rowIndex++;
		}
		// 自动调整宽度
		/*
		 * for (int i = 0; i < headers.length; i++) { sheet.autoSizeColumn(i); }
		 */
		try {
			workbook.write(out);
			workbook.close();
			workbook.dispose();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) throws IOException {

		String[] headMap = new String[] { "字段1", "字段2", "字段3" };

		JSONArray jsonArray = new JSONArray();

		for (int i = 0; i < 5; i++) {
			JSONObject j = new JSONObject();
			j.put("字段1", "字段1：" + (i + 1));
			j.put("字段2", "字段2：" + (i + 1));
			j.put("字段3", "字段3：" + (i + 1));

			jsonArray.add(j);
		}

		File file = new File("C:\\Users\\linNaibin\\Desktop\\test.xlsx");
		if (!file.exists()) {
			file.createNewFile();
		}
		OutputStream out = new FileOutputStream(file);

		buildxlsx(headMap, jsonArray, out);

	}

}
